use reqwest::Url;
use serde::{Deserialize, Serialize};
use serde_json::Value;
use sqlx::{Encode, Error, Execute, FromRow, Pool, QueryBuilder, Row, Sqlite, Type};
use sqlx::query::Query;
use sqlx::sqlite::{SqliteQueryResult, SqliteRow};
use tracing::{error, info, warn};
use crate::models::bili::tag::BiliPictureTagModel;

pub const BILI_ALBUM_DDL: &str = r#"
create table if not exists bili_album
(
    doc_id      integer     not null,
    poster_uid  integer     not null,
    title       text        not null,
    description text        not null,
    pictures    text        not null,
    count       integer     not null,
    ctime       integer     not null,
    view        integer     not null,
    like        integer     not null,
    dyn_id      varchar(63) not null primary key,
    collected   bool        not null,
    deleted     bool        not null,
    check ((JSON_VALID("pictures") OR "pictures" IS NULL))
);
"#;

/// 哔哩哔哩图片
#[derive(Debug, Clone, Serialize, Deserialize)]
pub struct BiliAlbumPicture {
    pub img_src: String,
    pub img_width: i64,
    pub img_height: i64,
    pub img_size: f64,
    pub img_tags: Option<Vec<BiliPictureTagModel>>,
}

/// 哔哩哔哩动态
#[derive(Debug, Clone, Serialize, Deserialize, PartialEq, Eq, FromRow)]
pub struct BiliAlbumModel {
    pub doc_id: i64,
    pub poster_uid: i64,
    pub title: String,
    pub description: String,
    pub pictures: String,
    pub count: i64,
    pub ctime: i64,
    pub view: i64,
    pub like: i64,
    pub dyn_id: String,
    pub collected: bool,
    pub deleted: bool,
}

impl From<&Value> for BiliAlbumModel {
    fn from(value: &Value) -> Self {
        Self {
            doc_id: value
                .get("doc_id")
                .unwrap()
                .as_i64()
                .expect("Doc id as i64 failed"),
            poster_uid: value
                .get("poster_uid")
                .unwrap()
                .as_i64()
                .expect("PID as i64 failed"),
            title: value.get("title").unwrap().as_str().unwrap().to_string(),
            description: value
                .get("description")
                .unwrap()
                .as_str()
                .unwrap()
                .to_string(),
            pictures: match value.get("pictures") {
                Some(pics_val) => {
                    let pics_str = format!("{}", pics_val);
                    info!("pics_str = {}", pics_str);
                    pics_str
                }
                None => {
                    warn!("Request bili album pictures failed");
                    "[]".to_string()
                }
            },
            count: value.get("count").unwrap().as_i64().unwrap(),
            ctime: value.get("ctime").unwrap().as_i64().unwrap(),
            view: value.get("view").unwrap().as_i64().unwrap(),
            like: value.get("like").unwrap().as_i64().unwrap(),
            dyn_id: value.get("dyn_id").unwrap().as_str().unwrap().to_string(),
            collected: value.get("collected")
                .unwrap_or(&Value::Bool(false))
                .as_bool()
                .unwrap_or(false),
            deleted: value.get("deleted")
                .unwrap_or(&Value::Bool(false))
                .as_bool()
                .unwrap_or(false),
        }
    }
}

impl From<Value> for BiliAlbumModel {
    fn from(value: Value) -> Self {
        Self::from(&value)
    }
}

impl TryFrom<&SqliteRow> for BiliAlbumModel {
    type Error = Error;

    fn try_from(row: &SqliteRow) -> Result<Self, Self::Error> {
        Ok(Self {
            doc_id: row.try_get::<i64, _>("doc_id")?,
            poster_uid: row.try_get::<i64, _>("poster_uid")?,
            title: row.try_get::<String, _>("title")?,
            description: row.try_get::<String, _>("description")?,
            pictures: row.try_get::<String, _>("pictures")?,
            count: row.try_get::<i64, _>("count")?,
            ctime: row.try_get::<i64, _>("ctime")?,
            view: row.try_get::<i64, _>("view")?,
            like: row.try_get::<i64, _>("like")?,
            dyn_id: row.try_get::<String, _>("dyn_id")?,
            collected: row.try_get::<bool, _>("collected")?,
            deleted: row.try_get::<bool, _>("deleted")?,
        })
    }
}

impl TryFrom<SqliteRow> for BiliAlbumModel {
    type Error = Error;

    fn try_from(row: SqliteRow) -> Result<Self, Self::Error> {
        Self::try_from(&row)
    }
}

impl BiliAlbumModel {
    pub fn new(
        doc_id: i64,
        poster_uid: i64,
        title: &str,
        description: &str,
        pictures: &str,
        count: i64,
        ctime: i64,
        view: i64,
        like: i64,
        dyn_id: &str,
        collected: bool,
        deleted: bool,
    ) -> Self {
        Self {
            doc_id,
            poster_uid,
            title: title.to_string(),
            description: description.to_string(),
            pictures: pictures.to_string(),
            count,
            ctime,
            view,
            like,
            dyn_id: dyn_id.to_string(),
            collected,
            deleted,
        }
    }

    pub async fn count(db: &Pool<Sqlite>) -> i64 {
        let mut qb = QueryBuilder::new("select count(ROWID) as count from bili_album;");
        match qb.build().fetch_one(db).await {
            Ok(row) => row.get::<i64, _>("count"),
            Err(err) => {
                error!("{}", err);
                0
            }
        }
    }

    pub async fn select_by_column<'a, T>(db: &Pool<Sqlite>, col: &str, val: T)
                                         -> Result<Vec<Self>, Error>
        where T: Send + Encode<'a, Sqlite> + Type<Sqlite> + 'a
    {
        let mut qb = QueryBuilder::new("select * from bili_album");
        qb.push(" where")
            .push(format!(" {col}="))
            .push_bind(val);
        qb.build_query_as()
            .fetch_all(db)
            .await
            .map_err(Error::from)
    }

    pub async fn select_all(db: &Pool<Sqlite>, include_deleted: bool)
                            -> Result<Vec<Self>, Error> {
        let mut qb = QueryBuilder::new("select * from bili_album");
        if !include_deleted {
            qb.push(" where")
                .push(" deleted=")
                .push_bind(0);
        }
        let queryset = qb.build().fetch_all(db).await?;
        let albums = queryset.iter()
            .map(BiliAlbumModel::try_from)
            .filter_map(|x| x.ok())
            .collect::<Vec<BiliAlbumModel>>();
        Ok(albums)
    }

    pub async fn select_latest_album(db: &Pool<Sqlite>) -> Option<Self> {
        let mut qb = QueryBuilder::new("select * from bili_album order by ctime desc limit 1");
        match qb.build().fetch_one(db).await {
            Err(err) => {
                error!("{}", err);
                None
            }
            Ok(row) => {
                if let Ok(album) = Self::try_from(row) {
                    Some(album)
                } else {
                    None
                }
            }
        }
    }

    pub async fn insert(db: &Pool<Sqlite>, entity: &Self) -> Result<SqliteQueryResult, Error> {
        sqlx::query(
            r#"insert into bili_album
            (doc_id, poster_uid, title, description, pictures, count, ctime, view, like, dyn_id, collected, deleted)
            values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);"#
        )
            .bind(entity.doc_id)
            .bind(entity.poster_uid)
            .bind(&entity.title)
            .bind(&entity.description)
            .bind({
                if entity.pictures.is_empty() { "[]" } else { &entity.pictures }
            })
            .bind(entity.count)
            .bind(entity.ctime)
            .bind(entity.view)
            .bind(entity.like)
            .bind(&entity.dyn_id)
            .bind(entity.collected)
            .bind(entity.deleted)
            .execute(db)
            .await
    }

    pub async fn insert_batch(db: &Pool<Sqlite>, entities: &[Self], batch_size: usize) -> anyhow::Result<SqliteQueryResult> {
        let mut offset = 0;
        let mut res_arr: Vec<SqliteQueryResult> = Vec::new();
        while offset < entities.len() {
            let mut tail = offset + batch_size;
            if tail >= entities.len() { tail = entities.len(); }
            let batch = &entities[offset..tail];

            let mut qb: QueryBuilder<Sqlite> = QueryBuilder::new("insert into bili_album(doc_id, poster_uid, title, description, pictures, count, ctime, view, like, dyn_id, collected, deleted) ");
            qb.push_values(batch.iter(), |mut e, rec| {
                e.push_bind(rec.doc_id)
                    .push_bind(rec.poster_uid)
                    .push_bind(&rec.title)
                    .push_bind(&rec.description)
                    .push_bind(&rec.pictures)
                    .push_bind(rec.count)
                    .push_bind(rec.ctime)
                    .push_bind(rec.view)
                    .push_bind(rec.like)
                    .push_bind(&rec.dyn_id)
                    .push_bind(rec.collected)
                    .push_bind(rec.deleted);
            });

            let query = qb.build();
            info!("{}", query.sql());
            let query_res = query.execute(db).await?;
            res_arr.push(query_res);
            offset += batch_size;
        }
        let mut res = SqliteQueryResult::default();
        res.extend(res_arr);
        Ok(res)
    }

    pub async fn delete_by_column<'a, T>(db: &Pool<Sqlite>, col: &str, val: T) -> Result<SqliteQueryResult, Error>
        where T: Send + Encode<'a, Sqlite> + Type<Sqlite> + 'a
    {
        let mut qb = QueryBuilder::new("delete from bili_album");
        qb.push(" where ")
            .push(col)
            .push("=")
            .push_bind(val);
        qb.build()
            .execute(db)
            .await
    }

    pub async fn update_column<'a, T>(db: &Pool<Sqlite>, entity: &Self, col: &str, val: T)
                                      -> Result<SqliteQueryResult, Error>
        where T: Send + Encode<'a, Sqlite> + Type<Sqlite> + 'a
    {
        let mut qb = QueryBuilder::new("update bili_album set ");
        qb.push(col)
            .push("=")
            .push_bind(val)
            .push(" where ")
            .push("dyn_id=")
            .push_bind(entity.dyn_id.clone());
        qb.build()
            .execute(db)
            .await
    }

    pub async fn update_entity(db: &Pool<Sqlite>, entity: &Self)
                               -> Result<SqliteQueryResult, Error>
    {
        let mut qb = QueryBuilder::new("update bili_album set ");
        qb.push("doc_id=").push_bind(entity.doc_id)
            .push(",poster_uid=").push_bind(entity.poster_uid)
            .push(",title=").push_bind(&entity.title)
            .push(",description=").push_bind(&entity.description)
            .push(",pictures=").push_bind(&entity.pictures)
            .push(",count=").push_bind(entity.count)
            .push(",ctime=").push_bind(entity.ctime)
            .push(",view=").push_bind(entity.view)
            .push(",like=").push_bind(entity.like)
            .push(" where dyn_id=").push_bind(&entity.dyn_id);
        qb.build()
            .execute(db)
            .await
    }
}

#[cfg(test)]
mod tests {
    use std::ops::Deref;
    use std::str::FromStr;
    use std::sync::Arc;

    use async_std::sync::Mutex;
    use async_std::task::block_on;
    use lazy_static::lazy_static;
    use sqlx::{Pool, Sqlite};
    use sqlx::sqlite::SqlitePoolOptions;
    use crate::log::init_logger;

    use crate::models::bili::album::{BILI_ALBUM_DDL, BiliAlbumModel};

    lazy_static! {
        static ref DB: Arc<Mutex<Pool<Sqlite>>> = Arc::new(
        Mutex::new(
            block_on(
                async {
                        let pool = SqlitePoolOptions::new()
                        .max_connections(16)
                        .connect("sqlite://:memory:")
                        .await
                        .expect("Connect database failed");
                        sqlx::query(BILI_ALBUM_DDL).execute(&pool).await.unwrap();
                        pool
                    }
                )
            )
        );
    }

    #[async_std::test]
    async fn test_bili_album_crud() {
        let db = DB.lock().await;
        let album = BiliAlbumModel::new(0, 0, "", "", "[]", 0, 0, 0, 0, "0", false, false);

        let res = BiliAlbumModel::insert(db.deref(), &album).await;
        assert_eq!(res.unwrap().rows_affected(), 1);

        let res = BiliAlbumModel::update_column(db.deref(), &album, "title", "test title").await;
        assert_eq!(res.unwrap().rows_affected(), 1);

        let res = BiliAlbumModel::update_entity(db.deref(), &album).await;
        assert_eq!(res.unwrap().rows_affected(), 1);

        let query = BiliAlbumModel::select_by_column(db.deref(), "dyn_id", "0").await;
        let query = query.unwrap().pop().unwrap();
        assert_eq!(album, query);

        let res = BiliAlbumModel::delete_by_column(db.deref(), "dyn_id", album.dyn_id).await.unwrap();
        assert_eq!(res.rows_affected(), 1_u64);

        let albums = vec![
            BiliAlbumModel::new(0, 0, "", "", "[]", 0, 1, 0, 0, "1", false, false),
            BiliAlbumModel::new(0, 0, "", "", "[]", 0, 2, 0, 0, "2", false, false),
            BiliAlbumModel::new(0, 0, "", "", "[]", 0, 3, 0, 0, "3", false, false),
        ];
        let query_result = BiliAlbumModel::insert_batch(db.deref(), &albums, 2).await;
        assert_eq!(query_result.unwrap().rows_affected(), albums.len() as u64);

        let queryset = BiliAlbumModel::select_all(db.deref(), false).await;
        assert_eq!(albums, queryset.unwrap());

        let album = BiliAlbumModel::select_latest_album(db.deref()).await;
        assert!(album.is_some());
        let album = album.unwrap();
        assert_eq!(album.ctime, 3);
    }

    #[test]
    fn test_bili_album_from() {
        init_logger();
        let json = r#"[{"doc_id":311337943,"poster_uid":357515451,"title":"","description":"对于天文爱好者来说...","pictures":[{"img_src":"https://i0.hdslb.com/bfs/new_dyn/07b0c56b3992656578949fa1322dfb77357515451.jpg","img_width":900,"img_height":11978,"img_size":3359.739990234375,"img_tags":null}],"count":1,"ctime":1710815305,"view":225684,"like":801,"dyn_id":"910433874515853313"}]"#;
        let value = serde_json::Value::from_str(json);
        assert!(value.is_ok());
        let value = value.unwrap();
        assert!(value.is_array());
        let album_arr = value.as_array().unwrap();
        let album_arr = album_arr.iter()
            .map(|x| BiliAlbumModel::try_from(x).unwrap())
            .collect::<Vec<BiliAlbumModel>>();
        assert_eq!(album_arr[0].pictures.as_str(), r#"[{"img_height":11978,"img_size":3359.739990234375,"img_src":"https://i0.hdslb.com/bfs/new_dyn/07b0c56b3992656578949fa1322dfb77357515451.jpg","img_tags":null,"img_width":900}]"#);
    }
}